﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DBbase
    {
        //从启动项目配置文件中 读取 数据库连接语句
        public static string sqlConStr = System.Configuration.ConfigurationManager.ConnectionStrings["smartGardenConnectinString"].ConnectionString.ToString();

        //实例化连接对象 con
        SqlConnection con = new SqlConnection(sqlConStr);

        #region 检测连接是否打开 -chkConnection()
        /// <summary>
        /// 检测连接是否打开
        /// </summary>
        private void chkConnection()
        {
            if (this.con.State == ConnectionState.Closed)
            {
                this.con.Open();
            }
        } 
        #endregion

        #region 执行插入语句,没有返回值  +ExecuteNonQuery(string strSQL)
        /// <summary>
        /// 执行插入语句
        /// </summary>
        /// <param name="strSql">sql插入语句</param>
        public bool ExecuteNonQuery(string strSQL)
        {
            chkConnection();
            try
            {
                SqlCommand com = new SqlCommand(strSQL, con);
                com.CommandType = CommandType.Text;
                com.CommandText = strSQL;
                com.ExecuteNonQuery();
                con.Close();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 执行语句，返回该语句查询的数据行的总数 +returnRowCount(string strSql)
        /// <summary>
        /// 执行语句，返回该语句查询的数据行的总数
        /// </summary>
        /// <param name="strSql">sql查询语句</param>
        /// <returns></returns>
        public int returnRowCount(string strSql)
        {
            try
            {
                chkConnection();
                SqlDataAdapter da = new SqlDataAdapter(strSql, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0].Rows.Count;
            }
            catch
            {
                return 0;
            }
            finally
            {
                con.Close();
            }
        } 
        #endregion

        #region 执行查询语句，返回DataSet结果集 +returnDataSet(string strSQL)
        /// <summary>
        /// 执行查询语句，返回DataSet结果集
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <returns></returns>
        public DataSet returnDataSet(string strSQL)
        {
            chkConnection();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(strSQL, con);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        } 
        #endregion

        #region 执行语句，返回一个结果 +returnOneResult(string strSQL)
        /// <summary>
        /// 执行语句，返回一个结果
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public int returnOneResult(string strSQL)
        {
            chkConnection();
            SqlCommand com = new SqlCommand(strSQL, con);
            con.Close();
            return (int)com.ExecuteScalar();
        } 
        #endregion

        public SqlDataReader returnDataReader(string strSQL)
        {
            chkConnection();

            SqlCommand cmd = new SqlCommand(strSQL, con);
            SqlDataReader reader = cmd.ExecuteReader();
            //con.Close();
            return reader;
        }
    }
}
